package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblContractInvoicesmanagemenMySql;

public class TblContractInvoicesmanagemenMapperSqlMySqlConfig {


    public String findInvoiceInfoListForCollection(PageInfo<TblContractInvoicesmanagemenMySql> pageInfo, TblContractInvoicesmanagemenMySql invoice) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,CAST(@ROW := @ROW + 1 AS SIGNED) AS RNUM FROM (SELECT INVOICEID,INVOICENO,INVOICEMONEY,INVOICETYPE,INVOICEDATE,INVOICESTATUS,INVOICEKPORG,INVOICEHEADTEXT  FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEOGR = " + invoice.getInvoiceogr() + " AND INVOICESTATUS != 5 AND INVOICEID NOT IN (SELECT TCC.INVOICEID FROM TBL_CONTRACT_COLLECTION TCC WHERE LINKORG = " + invoice.getInvoiceogr() + " AND INVOICEID IS NOT NULL UNION ALL SELECT INVOICEID FROM TBL_CONTRACT_PAYMENT WHERE LINKORG = " + invoice.getInvoiceogr() + " AND INVOICEID IS NOT NULL)");
        if (invoice.getInvoiceno() != null && !"".equals(invoice.getInvoiceno())) {
            sbSql.append(" AND INVOICENO LIKE '%" + invoice.getInvoiceno() + "%'");
        }
        if (invoice.getInvoiceheadtext() != null && !"".equals(invoice.getInvoiceheadtext())) {
            sbSql.append(" AND INVOICEHEADTEXT LIKE '%" + invoice.getInvoiceheadtext() + "%'");
        }
        if (invoice.getInvoicekporg() != null && !"".equals(invoice.getInvoicekporg())) {
            sbSql.append(" AND INVOICEKPORG LIKE '%" + invoice.getInvoicekporg() + "%'");
        }
        if (invoice.getInvoicetype() != null && !"".equals(invoice.getInvoicetype())) {
            sbSql.append(" AND INVOICETYPE = '" + invoice.getInvoicetype() + "'");
        }
        if (invoice.getBudgetId() != null) {
            sbSql.append(" AND BUDGETID = " + invoice.getBudgetId());
        }

        sbSql.append(" ORDER BY INVOICEID DESC) BUDGET limit " + pageInfo.getCurrentRecord() + " , " + pageInfo.getPageSize() + " ) as a ");
        String sql = sbSql.toString();
        return sql;
    }


    public String findInvoiceInfoListForCollectionCount(TblContractInvoicesmanagemenMySql invoice) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEOGR = " + invoice.getOrgid() + " AND INVOICESTATUS != 5 AND INVOICEID NOT IN (SELECT TCC.INVOICEID FROM TBL_CONTRACT_COLLECTION TCC WHERE LINKORG = " + invoice.getInvoiceogr() + " AND INVOICEID IS NOT NULL UNION ALL SELECT INVOICEID FROM TBL_CONTRACT_PAYMENT WHERE LINKORG = " + invoice.getInvoiceogr() + " AND INVOICEID IS NOT NULL)");
        if (invoice.getInvoiceno() != null && !"".equals(invoice.getInvoiceno())) {
            sbSql.append(" AND INVOICENO LIKE '%" + invoice.getInvoiceno() + "%'");
        }
        if (invoice.getInvoiceheadtext() != null && !"".equals(invoice.getInvoiceheadtext())) {
            sbSql.append(" AND INVOICEHEADTEXT LIKE '%" + invoice.getInvoiceheadtext() + "%'");
        }
        if (invoice.getInvoicekporg() != null && !"".equals(invoice.getInvoicekporg())) {
            sbSql.append(" AND INVOICEKPORG LIKE '%" + invoice.getInvoicekporg() + "%'");
        }
        if (invoice.getInvoicetype() != null && !"".equals(invoice.getInvoicetype())) {
            sbSql.append(" AND INVOICETYPE = '" + invoice.getInvoicetype() + "'");
        }
        if (invoice.getBudgetId() != null) {
            sbSql.append(" AND BUDGETID = " + invoice.getBudgetId());
        }
        // sbSql.append(" ORDER BY INVOICEID DESC)");
        String sql = sbSql.toString();
        return sql;
    }


    public String findContractInvociesManaeMenPageInfo(PageInfo<TblContractInvoicesmanagemenMySql> pageInfo, TblContractInvoicesmanagemenMySql invoice) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,CAST(@ROW := @ROW + 1 AS SIGNED) AS RNUM FROM (SELECT tcp.BUDGETNAME budgetName, ts.REALNAME realname, tci.*  FROM TBL_CONTRACT_INVOICESMANAGEMEN tci\n" +
                "left join TBL_STAFF ts on tci.CREATESTAFF = ts.STAFFID LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tci.BUDGETID = tcp.BUDGETID \n" +
                "WHERE INVOICEOGR = " + invoice.getInvoiceogr());
        if (invoice.getCreatestaff() != null) {
            sbSql.append(" AND CREATESTAFF = " + invoice.getCreatestaff());
        }

        if (invoice.getInvoiceno() != null && !"".equals(invoice.getInvoiceno())) {
            sbSql.append(" AND INVOICENO LIKE '%" + invoice.getInvoiceno() + "%'");
        }

        if (invoice.getInvoiceheadtext() != null && !"".equals(invoice.getInvoiceheadtext())) {
            sbSql.append(" AND INVOICEHEADTEXT LIKE '%" + invoice.getInvoiceheadtext() + "%'");
        }
        if (invoice.getInvoicecontent() != null && !"".equals(invoice.getInvoicecontent())) {
            sbSql.append(" AND INVOICECONTENT LIKE '%" + invoice.getInvoicecontent() + "%'");
        }

        if (invoice.getInvoicetype() != null && !"".equals(invoice.getInvoicetype())) {
            sbSql.append(" AND INVOICETYPE = '" + invoice.getInvoicetype() + "'");
        }

        if (invoice.getInvoicestatus() != null) {
            sbSql.append(" AND INVOICESTATUS = " + invoice.getInvoicestatus());
        }

        sbSql.append(" ORDER BY INVOICEID DESC) BUDGET LIMIT " + pageInfo.getCurrentRecord() + " , " + pageInfo.getPageSize() + " ) AS A ");
        String sql = sbSql.toString();
        return sql;
    }

    public String findContractInvociesManaeMenPageInfoCount(TblContractInvoicesmanagemenMySql invoice) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_INVOICESMANAGEMEN tci\n" +
                "left join TBL_STAFF ts on tci.CREATESTAFF = ts.STAFFID\n" +
                "WHERE INVOICEOGR = " + invoice.getInvoiceogr());
        if (invoice.getCreatestaff() != null) {
            sbSql.append(" AND CREATESTAFF = " + invoice.getCreatestaff());
        }

        if (invoice.getInvoiceno() != null && !"".equals(invoice.getInvoiceno())) {
            sbSql.append(" AND INVOICENO = '%" + invoice.getInvoiceno() + "%'");
        }

        if (invoice.getInvoiceheadtext() != null && !"".equals(invoice.getInvoiceheadtext())) {
            sbSql.append(" AND INVOICEHEADTEXT LIKE '%" + invoice.getInvoiceheadtext() + "%'");
        }

        if (invoice.getInvoicetype() != null && !"".equals(invoice.getInvoicetype())) {
            sbSql.append(" AND INVOICETYPE = '" + invoice.getInvoicetype() + "'");
        }

        if (invoice.getInvoicestatus() != null) {
            sbSql.append(" AND INVOICESTATUS = " + invoice.getInvoicestatus());
        }

        sbSql.append(" ORDER BY INVOICEID DESC");
        String sql = sbSql.toString();
        return sql;
    }


    public String selectListCount(TblContractInvoicesmanagemenMySql oldInvoice) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEOGR = " + oldInvoice.getInvoiceogr() + " AND INVOICENO = '" + oldInvoice.getInvoiceno() + "'");
        if (oldInvoice.getInvoiceid() != null) {
            sbSql.append(" AND INVOICEID != " + oldInvoice.getInvoiceid());
        }
        String sql = sbSql.toString();
        return sql;
    }


    public String updateMergenEntity(TblContractInvoicesmanagemenMySql oldInvoice) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_CONTRACT_INVOICESMANAGEMEN SET INVOICENO = " + oldInvoice.getInvoiceno() + " ");

        if (oldInvoice.getBudgetId() != null && !"".equals(oldInvoice.getBudgetId())) {
            sql.append(" , BUDGETID = '" + oldInvoice.getBudgetId() + "'");
        }
        if (oldInvoice.getInvoicemoney() != null && !"".equals(oldInvoice.getInvoicemoney())) {
            sql.append(" , INVOICEMONEY = '" + oldInvoice.getInvoicemoney() + "'");
        }
        if (oldInvoice.getInvoiceheadtext() != null && !"".equals(oldInvoice.getInvoiceheadtext())) {
            sql.append(" , INVOICEHEADTEXT = '" + oldInvoice.getInvoiceheadtext() + "'");
        }
        if (oldInvoice.getInvoicepost() != null && !"".equals(oldInvoice.getInvoicepost())) {
            sql.append(" , INVOICEPOST = '" + oldInvoice.getInvoicepost() + "'");
        }
        if (oldInvoice.getInvoicecontent() != null && !"".equals(oldInvoice.getInvoicecontent())) {
            sql.append(" , INVOICECONTENT = '" + oldInvoice.getInvoicecontent() + "'");
        }
        if (oldInvoice.getInvoicestatus() != null && !"".equals(oldInvoice.getInvoicestatus())) {
            sql.append(" , INVOICESTATUS = '" + oldInvoice.getInvoicestatus() + "'");
        }
        if (oldInvoice.getInvoicespdate() != null && !"".equals(oldInvoice.getInvoicespdate())) {
            sql.append(" , INVOICESPDATE = TO_DATE('" + DateUtil.parseDate(oldInvoice.getInvoicedate(), "yyyy-MM-dd HH:mm:ss") + "'");
        }
        if (oldInvoice.getInvoicesporg() != null && !"".equals(oldInvoice.getInvoicesporg())) {
            sql.append(" , INVOICESPORG = '" + oldInvoice.getInvoicesporg() + "'");
        }
        if (oldInvoice.getInvoicekporg() != null && !"".equals(oldInvoice.getInvoicekporg())) {
            sql.append(" , INVOICEKPORG = '" + oldInvoice.getInvoicekporg() + "'");
        }
        if (oldInvoice.getInvoiceogr() != null && !"".equals(oldInvoice.getInvoiceogr())) {
            sql.append(" , INVOICEOGR = '" + oldInvoice.getInvoiceogr() + "'");
        }
        if (oldInvoice.getCreatestaff() != null && !"".equals(oldInvoice.getCreatestaff())) {
            sql.append(" , CREATESTAFF = '" + oldInvoice.getCreatestaff() + "'");
        }
        if (oldInvoice.getTinumber() != null && !"".equals(oldInvoice.getTinumber())) {
            sql.append(" , TINUMBER = '" + oldInvoice.getTinumber() + "'");
        }
        sql.append(" WHERE INVOICEID = '" + oldInvoice.getInvoiceid() + "'");
        return sql.toString();
    }


    public String saveMergenEntity(TblContractInvoicesmanagemenMySql oldInvoice) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_CONTRACT_INVOICESMANAGEMEN (INVOICEID");
        StringBuffer value = new StringBuffer(" VALUES ( "+oldInvoice.getInvoiceid()+"");

        if (oldInvoice.getBudgetId() != null && !"".equals(oldInvoice.getBudgetId())) {
            column.append(",BUDGETID");
            value.append(",'" + oldInvoice.getBudgetId() + "'");
        }
        if (oldInvoice.getInvoiceno() != null && !"".equals(oldInvoice.getInvoiceno())) {
            column.append(",INVOICENO");
            value.append(",'" + oldInvoice.getInvoiceno() + "'");
        }
        if (oldInvoice.getInvoicemoney() != null && !"".equals(oldInvoice.getInvoicemoney())) {
            column.append(",INVOICEMONEY");
            value.append(",'" + oldInvoice.getInvoicemoney() + "'");
        }
        if (oldInvoice.getInvoicedate() != null && !"".equals(oldInvoice.getInvoicedate())) {
            column.append(",INVOICEDATE");
            value.append(",TO_DATE('" + DateUtil.parseDate(oldInvoice.getInvoicedate(), "yyyy-MM-dd HH:mm:ss") + "'");
        }
        if (oldInvoice.getInvoiceheadtext() != null && !"".equals(oldInvoice.getInvoiceheadtext())) {
            column.append(",INVOICEHEADTEXT");
            value.append(",'" + oldInvoice.getInvoiceheadtext() + "'");
        }
        if (oldInvoice.getInvoicepost() != null && !"".equals(oldInvoice.getInvoicepost())) {
            column.append(",INVOICEPOST");
            value.append(",'" + oldInvoice.getInvoicepost() + "'");
        }
        if (oldInvoice.getInvoicecontent() != null && !"".equals(oldInvoice.getInvoicecontent())) {
            column.append(",INVOICECONTENT");
            value.append(",'" + oldInvoice.getInvoicecontent() + "'");
        }
        if (oldInvoice.getInvoicetype() != null && !"".equals(oldInvoice.getInvoicetype())) {
            column.append(",INVOICETYPE");
            value.append(",'" + oldInvoice.getInvoicetype() + "'");
        }
        if (oldInvoice.getInvoicestatus() != null && !"".equals(oldInvoice.getInvoicestatus())) {
            column.append(",INVOICESTATUS");
            value.append(",'" + oldInvoice.getInvoicestatus() + "'");
        }
        if (oldInvoice.getInvoicespdate() != null && !"".equals(oldInvoice.getInvoicespdate())) {
            column.append(",INVOICESPDATE");
            value.append(",TO_DATE('" + DateUtil.parseDate(oldInvoice.getInvoicespdate(), "yyyy-MM-dd HH:mm:ss") + "'");
        }
        if (oldInvoice.getInvoicesporg() != null && !"".equals(oldInvoice.getInvoicesporg())) {
            column.append(",INVOICESPORG");
            value.append(",'" + oldInvoice.getInvoicesporg() + "'");
        }
        if (oldInvoice.getInvoicekporg() != null && !"".equals(oldInvoice.getInvoicekporg())) {
            column.append(",INVOICEKPORG");
            value.append(",'" + oldInvoice.getInvoicekporg() + "'");
        }
        if (oldInvoice.getInvoiceogr() != null && !"".equals(oldInvoice.getInvoiceogr())) {
            column.append(",INVOICEOGR");
            value.append(",'" + oldInvoice.getInvoiceogr() + "'");
        }
        if (oldInvoice.getCreatestaff() != null && !"".equals(oldInvoice.getCreatestaff())) {
            column.append(",CREATESTAFF");
            value.append(",'" + oldInvoice.getCreatestaff() + "'");
        }
        if (oldInvoice.getTinumber() != null && !"".equals(oldInvoice.getTinumber())) {
            column.append(",TINUMBER");
            value.append(",'" + oldInvoice.getTinumber() + "'");
        }
        if (oldInvoice.getOutsideid() != null && !"".equals(oldInvoice.getOutsideid())) {
            column.append(",OUTSIDEID");
            value.append(",'" + oldInvoice.getOutsideid() + "'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString() + value.toString();
        return sql;
    }


    public String saveInvoicemanageMenTci(TblContractInvoicesmanagemenMySql tci) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_CONTRACT_INVOICESMANAGEMEN (INVOICEID");
        StringBuffer value = new StringBuffer(" VALUES ("+tci.getInvoiceid()+"");

//        if(tci.getBudgetId()!= null && !"".equals(tci.getBudgetId())) {
//            column.append(",BUDGETID");
//            value.append(",'"+tci.getBudgetId()+"'");
//        }
        if (tci.getInvoiceno() != null && !"".equals(tci.getInvoiceno())) {
            column.append(",INVOICENO");
            value.append(",'" + tci.getInvoiceno() + "'");
        }
        if (tci.getInvoicemoney() != null && !"".equals(tci.getInvoicemoney())) {
            column.append(",INVOICEMONEY");
            value.append(",'" + tci.getInvoicemoney() + "'");
        }
        if (tci.getInvoicedate() != null && !"".equals(tci.getInvoicedate())) {
            column.append(",INVOICEDATE");
            value.append(",TO_DATE('" + DateUtil.parseDate(tci.getInvoicedate(), "yyyy-MM-dd HH:mm:ss") + "'");
        }
        if (tci.getInvoiceheadtext() != null && !"".equals(tci.getInvoiceheadtext())) {
            column.append(",INVOICEHEADTEXT");
            value.append(",'" + tci.getInvoiceheadtext() + "'");
        }
        if (tci.getInvoicepost() != null && !"".equals(tci.getInvoicepost())) {
            column.append(",INVOICEPOST");
            value.append(",'" + tci.getInvoicepost() + "'");
        }
        if (tci.getInvoicecontent() != null && !"".equals(tci.getInvoicecontent())) {
            column.append(",INVOICECONTENT");
            value.append(",'" + tci.getInvoicecontent() + "'");
        }
        if (tci.getInvoicetype() != null && !"".equals(tci.getInvoicetype())) {
            column.append(",INVOICETYPE");
            value.append(",'" + tci.getInvoicetype() + "'");
        }
        if (tci.getInvoicestatus() != null && !"".equals(tci.getInvoicestatus())) {
            column.append(",INVOICESTATUS");
            value.append(",'" + tci.getInvoicestatus() + "'");
        }
        if (tci.getInvoicespdate() != null && !"".equals(tci.getInvoicespdate())) {
            column.append(",INVOICESPDATE");
            value.append(",TO_DATE('" + DateUtil.parseDate(tci.getInvoicespdate(), "yyyy-MM-dd HH:mm:ss") + "'");
        }
        if (tci.getInvoicesporg() != null && !"".equals(tci.getInvoicesporg())) {
            column.append(",INVOICESPORG");
            value.append(",'" + tci.getInvoicesporg() + "'");
        }
        if (tci.getInvoicekporg() != null && !"".equals(tci.getInvoicekporg())) {
            column.append(",INVOICEKPORG");
            value.append(",'" + tci.getInvoicekporg() + "'");
        }
        if (tci.getInvoiceogr() != null && !"".equals(tci.getInvoiceogr())) {
            column.append(",INVOICEOGR");
            value.append(",'" + tci.getInvoiceogr() + "'");
        }
        if (tci.getCreatestaff() != null && !"".equals(tci.getCreatestaff())) {
            column.append(",CREATESTAFF");
            value.append(",'" + tci.getCreatestaff() + "'");
        }
        if (tci.getTinumber() != null && !"".equals(tci.getTinumber())) {
            column.append(",TINUMBER");
            value.append(",'" + tci.getTinumber() + "'");
        }
        if (tci.getOutsideid() != null && !"".equals(tci.getOutsideid())) {
            column.append(",OUTSIDEID");
            value.append(",'" + tci.getOutsideid() + "'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString() + value.toString();
        return sql;
    }
}
